import math
import contextlib
import pymysql

from sqlalchemy import (exc, event, create_engine, Column, BigInteger, String, text, Sequence,
                        INTEGER, TIMESTAMP, DATETIME, FLOAT, DECIMAL, DATE)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql.expression import (or_, and_)

from app.setting import (DB_USER, DB_PASSWORD, DB_HOST, DB_PORT, DB_NAME, DB_ECHO)


def checkout_listener(dbapi_con, con_record, con_proxy):
    """
    Ensures that MySQL connections checked out of the pool are alive.
    """

    try:
        try:
            dbapi_con.ping(False)
        except TypeError:
            dbapi_con.ping()
    except dbapi_con.OperationalError as ex:
        if ex.args[0] in (2006, 2013, 2014, 2045, 2055):
            raise exc.DisconnectionError()
        else:
            raise


engine = create_engine(
    "mysql+pymysql://" + DB_USER + ":" + DB_PASSWORD + "@" + DB_HOST + ":" + str(
        DB_PORT) + "/" + DB_NAME + "?charset=utf8", echo=DB_ECHO)
# 增加监听
event.listen(engine, 'checkout', checkout_listener)
Base = declarative_base()
Session = sessionmaker(bind=engine)


@contextlib.contextmanager
def get_db_session():
    """
    定义上下文管理器，连接后自动关闭会话
    :return:
    """

    session = Session()
    try:
        yield session
    finally:
        try:
            session.commit()
        finally:
            session.close()


@contextlib.contextmanager
def get_db_conn():
    """
    定义上下文管理器，连接后自动关闭连接
    :return:
    """

    conn = engine.connect()
    try:
        yield conn
    finally:
        try:
            conn.commit()
        finally:
            conn.close()


class Page(object):
    """
    分页对象
    """

    def __init__(self, items, page, page_size, total):
        self.items = items
        self.previous_page = None
        self.next_page = None
        self.has_previous = page > 1
        if self.has_previous:
            self.previous_page = page - 1
        previous_items = (page - 1) * page_size
        self.has_next = previous_items + len(items) < total
        if self.has_next:
            self.next_page = page + 1
        self.total = total
        self.pages = int(math.ceil(total / float(page_size)))


def paginate(query, page, page_size):
    """
    分页方法
    :param query:
    :param page:
    :param page_size:
    :return:
    """

    if page <= 0:
        raise AttributeError('page needs to be >= 1')
    if page_size <= 0:
        raise AttributeError('page_size needs to be >= 1')
    items = query.limit(page_size).offset((page - 1) * page_size).all()
    total = query.order_by(None).count()
    return Page(items, page, page_size, total)
